/**
 * 
 */
package org.starsource.forum.server.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedHashSet;
import java.util.NoSuchElementException;
import java.util.Set;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import org.starsource.forum.client.exception.DataInconsistantException;
import org.starsource.forum.client.exception.DatabaseException;
import org.starsource.forum.client.exception.ResourceNotFoundException;
import org.starsource.forum.server.dao.ResourceDao;
import org.starsource.forum.server.domain.Message;
import org.starsource.forum.server.domain.Resource;

/**
 * @author james
 *
 */
public class ResourceDaoJdbc implements ResourceDao {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	private static org.apache.log4j.Logger logger = org.apache.log4j.LogManager
	.getLogger(ResourceDaoJdbc.class.getName());

	private class GroupRowCallbackHandler implements RowCallbackHandler {
		private Set<Resource> resourceSet;
		private Message message;
		
		public void processRow(ResultSet rs) throws SQLException {
			Resource resource = new Resource();
			resource.setId(rs.getInt("id"));
			resource.setUrl(rs.getString("url"));
			int messageId = rs.getInt("message_id");
			if (message == null) {
				resource.setMessageId(messageId);
			} else if (message.getId() == messageId){
				resource.setMessage(message);
			} else {
				throw new SQLException("Data inconsistant exception",new DataInconsistantException());
			}
			resource.setThumbUrl(rs.getString("thumb_url"));
			resource.setWidth(rs.getInt("width"));
			resource.setHeight(rs.getInt("height"));

			resourceSet.add(resource);
		}
		
		public GroupRowCallbackHandler(Set<Resource> resourceSet) {
			this.resourceSet = resourceSet;
		}

		public GroupRowCallbackHandler(Set<Resource> resourceSet, Message message) {
			this.resourceSet = resourceSet;
			this.message = message;
		}
	}
	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.ResourceDao#delete(org.starsource.forum.server.domain.Resource)
	 */
	@Override
	public void delete(final Resource resource) throws DatabaseException {
		try {
			final String sql = "DELETE FROM t_resource WHERE id = ?";
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, resource.getId());
					return ps;
				}
			});
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.ResourceDao#getResource(int)
	 */
	@Override
	public Resource getResource(int id) throws ResourceNotFoundException,
			DatabaseException {
		try {
			String sql = "SELECT * FROM t_resource WHERE id = ? ";
			final Set<Resource> resourceSet = new LinkedHashSet<Resource>();
			jdbcTemplate.query(sql, new Object[] {id}, new GroupRowCallbackHandler(resourceSet));
			return resourceSet.iterator().next();
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new ResourceNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.ResourceDao#save(org.starsource.forum.server.domain.Resource)
	 */
	@Override
	public void save(final Resource resource) throws DatabaseException {
		final String sql = "INSERT INTO t_resource(url, message_id, thumb_url, width, height) VALUES(?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); 
				
				ps.setString(1, resource.getUrl());
				ps.setInt(2, resource.getMessageId());
				ps.setString(3, resource.getThumbUrl());
				ps.setInt(4, resource.getWidth());
				ps.setInt(5, resource.getHeight());
				return ps;
			}
		}, keyHolder);
		resource.setId(keyHolder.getKey().intValue());
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.ResourceDao#update(org.starsource.forum.server.domain.Resource)
	 */
	@Override
	public void update(final Resource resource) throws DatabaseException {
		final String sql = "UPDATE t_resource set url = ?, message_id = ?, thumb_url = ?, width = ?, height = ?  WHERE id = ?";
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, resource.getUrl());
				ps.setInt(2, resource.getMessageId());
				ps.setString(3, resource.getThumbUrl());
				ps.setInt(4, resource.getWidth());
				ps.setInt(5, resource.getHeight());
				ps.setInt(6, resource.getId());
				return ps;
			}
		});
	}

	@Override
	public Set<Resource> getResourceSet(Message message)
			throws DatabaseException {
		try {
			String sql = "SELECT * FROM t_resource WHERE message_id = ? ORDER BY url ASC";
			final Set<Resource> resourceSet = new LinkedHashSet<Resource>();
			jdbcTemplate.query(sql, new Object[] {message.getId()}, new GroupRowCallbackHandler(resourceSet, message));
			return resourceSet;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new ResourceNotFoundException();
		}
	}

	@Override
	public void delete(final Message message) throws DatabaseException {
		try {
			final String sql = "DELETE FROM t_resource WHERE message_id = ?";
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, message.getId());
					return ps;
				}
			});
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

}
